Skip to main content

Demos

Screenshots: The image files referenced in the original demo notes are missing from the repository. The written activities are retained below.

Module 2​

  1. Open up Power BI, and in a new Power BI file use this workbook to "Get Data" from the spreadsheet "PA - CDs" (not: "CDs", but "PA - CDs". This is a special version of the CDs spreadsheet for these Practical Activities).

  2. Click on the fields "File extension" and Size.

  3. If this visualization is showing a bar chart, please change it to a Table.

  4. Save this page to your local hard drive, and publish it to the Power BI service if you want to.

Module 3​

Get the data from the spreadsheet "PA - CDs" (from the workbook "PowerBIData"), and add the fields "File extension" and Size from the Module 2 Demo.

Then:

  1. Check the default summarization for the "Size" field.
  2. Add the "Date created" field into the visual as well. Put it after the "File extension" but before the "Size" field.
  3. Has it created a detailed hierarchy? If so, right-hand click on "Date created" in the "Column" well and select "Date created" (instead of "Date hierarchy") It absolutely has created one..
  • Note: If it hasn't, then your system may have auto date hierarchies switched off. To switch it on, go to File > Options and Settings > Options > Data Load (Current File) > Auto Date/Time. You'll need to change it to the "date Created" by right clicking and selecting Date Created.

The resulting table should show the selected fields with the date formatted as month and year. 4. Change the summarization for "Date created" to "Earliest", just for this visualization (don't change the default).

  1. Change the default formatting of the "Date created" field so that it just shows month and year.

  2. Change the font size of the visualization.

  3. Change the colour of the header background.

  4. Change the format of the "Size" field so that it displays in Millions of bytes.

  5. Copy this visualization to create a second visualization, and alter this second visualization so that it has just two columns: Year of "Date created", and "Size".

  6. Move the first visualization so that the center of it is half way down the page, and move the second visualization so that the top of the visualizations are the same. Play around with these features to become excellent at this module. You'll need it to build onto the next demo.

Module 4​

  1. Create a Matrix, with Year of "Date created" in the Rows well, "File extension" in the Columns well, and "Size" in the Values well. Use the "alternating rows" style.

  2. Change the formatting of the "Size" in this visualization only so that it displays in Millions.

  3. Add "Quarter" into the Rows well. Have a look at the different options in Visual tools - Data/Drill - Data actions.

  4. Add a second visualization, a Stacked Column Chart, with "File extension" in the X-Axis (or Axis) well, and "Size" in the Y-Axis (or Value) well.

  5. Add a third visualization, an Area Chart, with Year and Quarter of "Date created" in the Axis well, and "Size" in the Value well. If you are seeing only years, click on "Expand all down one level in the hierarchy" to show both Year and Quarter.

  6. Edit the interactions, so that when you click on the first visualization, it filters (not highlights) the second visualization.

  7. In the first visualization, click on the year 2010, and see what happens. Play around with this as well.

Module 9: Maps​

Load the "PA - Maps" spreadsheet from the PowerBIData workbook.

This exercise is designed to help you understand how geographic data works in Power BI when you use drill-down, hierarchy levels, and different map layers. The goal is to become comfortable with map visuals, location accuracy, and how to interpret concentration and distribution patterns across regions.

  1. Create an Azure Map using CountryRegionCode as the first level and StateProvinceCode as the second level.

    • If your version of Power BI does not support Azure Maps, use the standard Map visual instead.

  2. Add Count of "AddressID" to the Size field.

    • This adds important context beyond location alone by showing how many records exist in each place.
    • It tells you not just where the data is, but how dense or significant each location is.

  3. Click "Go to the next level in the hierarchy".

    • Do you see dots in South America? If so, consider why this might be happening.
    • In many datasets, some records may include location codes that geocode outside the primary region of interest, which can create unexpected points.

  4. Drill back up and then click "Expand all down one level in the hierarchy".

    • Notice whether the Argentina point remains visible after the hierarchy expands.
    • This helps you understand how drill state and level-of-detail affect the data shown on the visual.

  5. Turn "Auto zoom" off.

    • This gives you more control over how the map is framed and makes it easier to compare areas at different zoom levels.

  6. Add City and PostalCode to the Location field and zoom in on one of these regions:

    • British Columbia, Canada
    • London, England
    • Paris, France
    • Melbourne, Australia
    • As you change the hierarchy level, notice how the dots move closer to their exact geographic locations.

  7. Keep clicking "Expand all down one level in the hierarchy".

    • Watch what happens to the placement of the dots as the map becomes more granular.
    • This demonstrates how drill-down reveals more precise geography inside the larger region.

  8. Turn "Category labels" on and zoom in and out.

    • Ask yourself: are the labels helpful, or do they make the map too busy?
    • The answer often depends on the data density and the level of zoom you are using.

  9. Zoom out fully, drill back up to the Country level, and change the map to a "Filled Map".

    • If you are not using Azure Maps, switch to the Filled Map visual instead.
    • Filled maps are useful when you want to compare regions by value rather than show individual bubbles.

  10. Change the default color in Data colors so that the map does not use a single color.

    • Use Conditional Formatting with a Gradient format style.
    • Base it on Count of "AddressID".
    • Apply a diverging color palette from blue to dark yellow to green.
    • This makes low, medium, and high values easy to distinguish.

  11. Click "Expand all down one level in the hierarchy" and then again.

    • What changes do you notice? Why?
    • As the level of detail increases, the map focuses on more specific geographic locations; this can change both the visible regions and the color intensity.

  12. Change the map back to the "Bubble Layer".

    • If you are using a standard map visual, switch back to the standard Map visual.
    • Bubble layers are useful when you want each location to show a size value, such as the number of records.

  13. Remove the location fields and create a new hierarchy called "Location" using:

    • CountryRegionCode
    • StateProvinceCode
    • City
    • PostalCode
    • Hide the original fields so the hierarchy becomes the main location field.

  14. Use Data Category to categorize the Country, State, City, and Postal Code fields correctly.

    • This helps Power BI understand the data type and how to map the values properly.
    • Then add the "Location" hierarchy back into the Location field.

  15. Change the map back to a standard "Map" and switch "Heat map" to On.

    • Click "Expand all down one level in the hierarchy" and then a second time.
    • Examine how the density patterns change as the map drills deeper.
    • Heat maps are especially valuable when you are looking for concentration and hotspots rather than individual points.

  16. Change the Heat Map colors so that the 0%, 50%, and 100% gradient stops are different shades of red.

    • This makes the low, medium, and high concentration areas easier to interpret at a glance.

This exercise helps you compare map layers and understand when to use bubbles, filled regions, and heat maps depending on the story you want to tell with your data.

Module 10: Measure Performance​

In this practice activity, you are working with a real-world safety dataset for the United States. Your task is to build a dashboard that helps the US National Highway Traffic Safety Administration (NHTSA) understand how road safety has improved over time, and how each state compares against a national target.

The time period is 2007-2018, and the target is 1.02 fatalities per 100,000,000 miles driven. This is a strong example of how Power BI can turn raw operational data into a performance dashboard with clear benchmarking and trend analysis.

To complete this exercise, use the spreadsheet "PA - Driving Safety" from the workbook "PowerBIData". The dataset includes:

  • State
  • Year
  • Miles traveled
  • Actual fatalities
  • Actual fatalities per 100,000,000 miles
  • Target fatality level
  • Actual fatalities per 100,000,000 miles for the final year in the dataset, 2018

This dashboard should include the following elements:

  1. A slicer to select a state.

    • Use the state field in a slicer.
    • In "Selection controls", set "Single Select" to On so the user can choose only one state at a time.

  2. A card showing the selected state.

    • This helps make the dashboard feel clear and user-friendly.
    • It tells the user exactly which state is currently being analyzed.

  3. A gauge showing the 2018 actual fatalities per 100,000,000 miles.

    • This should be measured against the target value of 1.02.
    • Set the gauge range from 0 to 2.5.
    • The target line should be at 1.02.
    • Use conditional color formatting so:
      • blue if below 0.8
      • yellow at 1.02
      • red at 2.50

  4. A map showing the selected state.

    • This should use a filled map so the selected state is highlighted by the same color logic used in the gauge.
    • This gives a geographic view of the state performance in context.

  5. A KPI visual for fatalities over time.

    • Use Fatalities as the indicator.
    • Use Year as the Trend Axis.
    • Set the target to the Average of Target Fatalities.
    • Use the KPI settings:
      • "Decreasing is positive"
      • "Low is good"
    • This helps show whether fatalities are improving over time compared to the benchmark.

  6. Two cards showing:

    • total miles driven
    • total fatalities

This dashboard should be designed to help the user quickly answer questions like:

  • Which state is performing best or worst?
  • Is the fatality rate improving over time?
  • Is the selected state below or above the target?
  • How much travel volume and how many fatalities are associated with the chosen state?

You should also create 3 additional charts on separate pages:

  1. A bar chart of fatalities over time.

    • Use conditional formatting to vary the bar color.
    • This helps show whether fatalities are rising or falling across the years.

  2. A bar chart showing the number of miles driven.

    • This helps tell the story of exposure: more miles driven may increase risk even if rates improve.

  3. A line and clustered column chart showing fatality rate as bars and the target value 1.02 as a line.

    • The bar should represent the fatality rate.
    • The line should be a constant value of 1.02 for comparison.
    • Hint: create a new measure or calculated column such as: Target1.02 = 1.02

These 3 charts should have a drill-through on State, and therefore should include a Back button.

  • You can drill through using the map.
  • This allows a user to click a state and navigate to a detailed page focused on that state.

This is a realistic business intelligence scenario because it combines:

  • time-series analysis
  • geographic comparison
  • performance benchmarking
  • KPI tracking
  • drill-through reporting

This is all based on real-world transportation safety data, including the target value. For more background, see: https://en.wikipedia.org/wiki/Transportation_safety_in_the_United_States

By the end of the exercise, the report should feel like a proper executive dashboard: it should communicate both the story and the context behind the numbers in a concise, decision-ready way.

Part One Demo

Build a Report from Import to Insight​

This end-to-end lab combines the practical activities in Demos.md with the concepts from Modules 1 through 11. It produces a small report that demonstrates data import, formatting, hierarchies, filters, navigation, maps, performance indicators, drillthrough, and validation.

Use Power BI Desktop and the PowerBIData workbook supplied with the course. The lab uses three independent datasets:

  • PA - CDs: table, matrix, chart, filtering, sorting, bookmarks, and pattern analysis.
  • PA - Maps: geographic hierarchies, data categories, bubble layers, filled maps, and heat maps.
  • PA - Driving Safety: slicers, cards, gauges, KPIs, conditional formatting, and drillthrough.

The datasets do not need to be related. Build each dataset on its own report pages and do not create artificial relationships between unrelated tables. Save the report as Part1-PowerBI-Demo.pbix.

1. Prepare the report​

  1. Open Power BI Desktop and create a new report.
  2. Use Get data > Excel and open the course PowerBIData workbook.
  3. Load PA - CDs, PA - Maps, and PA - Driving Safety. Inspect each preview before loading it.
  4. In the Data or Model view, check that date, numeric, text, and geographic fields have sensible data types.
  5. Categorize geographic fields from PA - Maps as Country, State or Province, City, and Postal Code where appropriate.
  6. Save the report locally as Part1-PowerBI-Demo.pbix.

2. Create the CDs exploration page​

Rename the first page CDs Overview. This page demonstrates Modules 2 through 6.

Detail table​

  1. Add a table visual with File extension, Date created, and Size.
  2. If Power BI adds Date created as a hierarchy, open the field menu and select the raw Date created field instead.
  3. Set the visual-only summarization of Date created to Earliest.
  4. Set Size to the aggregation that answers the question. Use Don't summarize for row-level detail or Sum when comparing total size.
  5. Format the date as month and year for the summary view and format Size in millions of bytes where the values are aggregated.
  6. Add a meaningful title, readable font size, a restrained header color, and useful alt text.

Matrix and charts​

  1. Add a matrix named Size by Year and Extension:
    • Rows: Year of Date created, then Quarter
    • Columns: File extension
    • Values: Size
  2. Apply an alternating-row style and display the values in millions.
  3. Add a stacked column chart named Size by Extension:
    • X-axis: File extension
    • Y-axis: Size
  4. Add an area chart named Size Over Time:
    • X-axis: Year and Quarter of Date created
    • Y-axis: Size
  5. Expand the date hierarchy so both Year and Quarter are visible.
  6. Configure the matrix interaction so selecting a year filters the column chart instead of merely highlighting it.
  7. Select 2010 and confirm that the dependent visual changes. Clear the selection and verify that the complete view returns.

Filters and slicers​

  1. Add a date slicer using Date created and a category slicer using File extension.
  2. Sync the slicers to the pages that should share the same context. Leave them unsynced on pages where an independent view is required.
  3. Add a visual-level filter to the table for a small set of extensions.
  4. Add a page-level filter for a date range and confirm that all page visuals respond.
  5. Test an advanced date condition and a TopN filter by total Size.
  6. Check the filter indicators before continuing so that no hidden filter changes the conclusions.

Sort, bookmarks, and layers​

  1. Sort the extension chart by total Size in descending order so the largest categories appear first.
  2. If a month name is used, create or use a month-number field and apply Sort by column so months appear January through December.
  3. Create a bookmark named All Files with all filters cleared.
  4. Create a bookmark named Large Files with a TopN or size filter applied.
  5. Create a Bookmark navigator so the two views can be switched without rebuilding the filters.
  6. Open the Selection pane, rename visuals descriptively, group related title and shape objects, and put interactive buttons in front of decorative objects.

3. Add pattern-analysis visuals​

Create a page named CDs Patterns. This page demonstrates Modules 8 and 11.

  1. Add a ribbon chart with a time field, File extension as the category or legend, and Size as the value. Use it to inspect changes in category rank over time.
  2. Add a waterfall chart with Path as the category and Size as the value if those fields are available in the CDs data. Use it to explain the positive and negative contributions to a total.
  3. Add a treemap with File extension as the category and Size as the value. Use it when the question is about proportional contribution.
  4. Create a quick measure for a common question such as running total, year-to-date total, or percentage of grand total. Place it in a chart, inspect the generated DAX, and verify that the result changes correctly with the slicers.
  5. Add a constant or average reference line to a supported chart to provide a benchmark.
  6. Export the data from one filtered visual. Confirm that the exported values respect the current slicers, filters, and aggregation.

4. Create the Maps page​

Rename a new page Geographic Analysis. This page follows the Maps exercise in Demos.md and demonstrates Module 9.

  1. Add an Azure Map using CountryRegionCode as the first hierarchy level and StateProvinceCode as the second. Use the standard Map visual if Azure Maps is unavailable.
  2. Add Count of AddressID to the size field.
  3. Drill to the next hierarchy level, then drill back up and use Expand all down one level. Observe how the visible points change.
  4. Turn Auto zoom off and inspect a selected region such as British Columbia, London, Paris, or Melbourne.
  5. Add City and PostalCode to the geographic hierarchy and expand through the levels to observe increasingly precise locations.
  6. Turn Category labels on and decide whether they improve the view at the current zoom level.
  7. Change to a filled map at the country level and apply conditional formatting based on Count of AddressID.
  8. Use a diverging, accessible color scale to distinguish low, medium, and high values. Do not rely only on red and green.
  9. Create a Location hierarchy containing CountryRegionCode, StateProvinceCode, City, and PostalCode. Hide the original fields if the hierarchy is now the preferred user-facing field.
  10. Return to a bubble map and enable the heat map option. Drill into the hierarchy and compare exact locations with density hotspots.
  11. Add a tooltip page named Location Tooltip containing the location and count of AddressID. Assign it to the map and test the hover behavior.

5. Build the Driving Safety dashboard​

Create a page named Safety Dashboard using PA - Driving Safety. The target fatality rate is 1.02 fatalities per 100,000,000 miles driven.

Measures and fields​

Use the supplied fields where possible. If a constant target measure is needed, create:

Target 1.02 = 1.02

Use the actual fatality rate field for the gauge and the target field or target measure for comparisons. Confirm the field names in the Data pane before creating calculations because course workbook versions may use slightly different names.

Dashboard visuals​

  1. Add a State slicer and turn Single select on.
  2. Add a card showing the selected state.
  3. Add a gauge for the 2018 actual fatalities per 100,000,000 miles:
    • Value: the 2018 actual fatality-rate field
    • Target: 1.02
    • Minimum: 0
    • Maximum: 2.5
  4. Apply conditional formatting so lower values are blue, the target area is yellow, and high values are red. Use the report's accessible palette consistently.
  5. Add a filled map showing the selected state and apply the same performance meaning to its colors.
  6. Add a KPI:
    • Indicator: Fatalities
    • Trend axis: Year
    • Target: Average of Target Fatalities
    • Direction: Decreasing is positive and Low is good
  7. Add cards for total miles driven and total fatalities.
  8. Add a clear title that includes the selected state through the card or slicer context.
  9. Test several states and confirm that every visual responds to the slicer. Clear the selection and decide whether the unfiltered state is a useful default.

6. Add safety detail and drillthrough​

Create three supporting pages:

  • Fatalities by Year: clustered bar chart with Year and Fatalities.
  • Miles by Year: bar chart with Year and Miles traveled.
  • Fatality Rate vs Target: line and clustered column chart with fatality rate as columns and the Target 1.02 measure as a line.

On each page:

  1. Apply conditional formatting to make changes in performance easy to see without relying only on color.
  2. Add State to the Drillthrough filters well.
  3. Add the automatic Back button and format it consistently.
  4. From the Safety Dashboard map or another State visual, right-click a state and drill through to each detail page.
  5. Confirm that the detail page shows only the selected state and that Back returns to the originating page.

Create a page navigator for the overview and detail pages. Add a bookmark named National View with no state filter and another named Selected State with a state selected. Test both the navigator and bookmark behavior in Reading view.

7. Validate the finished report​

Before publishing, complete this review:

  • Verify every visual has the intended field, aggregation, and filter context.
  • Check that dates sort chronologically and that numbers use appropriate units.
  • Test clear, single, and multiple selections in slicers and visuals.
  • Test page navigation, drillthrough, tooltip pages, bookmarks, and Back buttons.
  • Confirm that maps use correct Data Categories and that unexpected locations are understood.
  • Export a filtered table or chart and compare it with the visible report values.
  • Check keyboard tab order, alt text, readable contrast, and whether decorative objects are excluded from navigation.
  • Open the report in Reading view and inspect it at a normal page size.
  • Save the .pbix file, publish it to an authorized sandbox workspace, and verify the published copy separately from Desktop.

The completed demo should let a user move from a high-level safety KPI to a selected state, inspect geographic context, drill into the time series, and return to the overview. The CDs and mapping pages provide additional practice with visual choice, data exploration, and pattern discovery while keeping each dataset independent and interpretable.